List SQL  Examples
Employee Lists
Desk Lists
Asset Lists

The List SQL field will accept any standard SQL queries, including sorting and joins. Use the following notation to select database elements:

[_TableName].[FieldName]

For example, the following query will display employees who have been assigned to desks.

SELECT [_Employees].[LastName], [_Employees].[FirstName], [_Desks].[deskname], [_Floors].[FloorName] FROM [_Employees] INNER JOIN [_Desks] ON [_Employees].[Desk_id]=[_Desks].[desk_id]INNER JOIN [_Floors] ON [_Employees].[Floor_Id]=[_Floors].[floor_id]

You can learn more about SQL queries at w3schools.com.

Examples
Employee Lists


Firstname, Lastname, Security Group (getting security group names from the _SecurityGroups table):

SELECT [_Employees].[FirstName], [_Employees].[LastName], [_SecurityGroups].[Security Group] FROM [_Employees] LEFT JOIN [_SecurityGroups] ON [_Employees].[SecurityGroup_id] = [_SecurityGroups].[SecurityGroup_id]
Firstname, lastname, desk (getting deskname from the _Desks table):

SELECT [_Employees].[FirstName], [_Employees].[LastName], [_Desks].[deskname] FROM [_Employees] LEFT JOIN [_Desks] ON [_Employees].[Desk_id] = [_Desks].[desk_id]
Full name, department (merging firstname & lastname):

SELECT [FirstName] + ' ' + [LastName] AS Employee, [department] FROM [_Employees]
Full name, floor, desk (getting deskname from the _Desks table and floorname from the _Floors table):

SELECT [_Employees].[FirstName] + ' ' + [_Employees].[LastName] AS Employee, [_Floors].[FloorName], [_Desks].[deskname] FROM [_Employees] LEFT JOIN [_Floors] ON [_Employees].[Floor_Id] = [_Floors].[floor_id] LEFT JOIN [_Desks] ON [_Employees].[Desk_id] = [_Desks].[desk_id]
Firstname, lastname, department, position, email, extension:

SELECT [FirstName], [LastName], [department], [position], [Email], [Extension] FROM [_Employees]
List only employees in the IT Department:

SELECT [LastName], [FirstName], [department] FROM [_Employees] WHERE [department] = 'IT'
List only employees in NY (using state field):

SELECT [LastName], [FirstName], [State] FROM [_Employees] WHERE [State] = 'NY'
List only employees on Floor 1:

SELECT [_Employees].[LastName], [_Employees].[FirstName], [_Floors].[FloorName] FROM [_Employees] LEFT JOIN [_Floors] ON [_Employees].[Floor_Id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'


Desk Lists
Desk name, floor name:

SELECT [_Desks].[deskname], [_Floors].[FloorName] FROM [_Desks] LEFT JOIN [_Floors] ON [_Desks].[floor_id] = [_Floors].[floor_id]
Desk name, seats:

SELECT [deskname], [Seats] FROM _Desks
List only meeting rooms (based on category):

SELECT [deskname], [Category] FROM [_Desks] WHERE [category] = 'Meeting Room'
List only desks on Floor 1:

SELECT [name] FROM {_Desks] WHERE [floor_id] = '201804280603417960'
or

SELECT [_Desks].[deskname], [_Floors].[FloorName] FROM [_Desks] LEFT JOIN [_Floors] ON [_Desks].[floor_id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'


Asset Lists
Asset name, floor name:

SELECT [_Assets].[name], [_Floors].[FloorName] FROM [_Assets] LEFT JOIN [_Floors] ON [_Assets].[floor_id] = [_Floors].[floor_id]
Asset name, model:

SELECT [name],[Model] FROM [_Assets]
List only printers (based on category):

SELECT [name],[category] FROM [_Assets] WHERE [category] = 'Printer'
List only assets on Floor 1:

SELECT [name] FROM {_Assets] WHERE [floor_id] = '201804280603417960'
or

SELECT [_Assets].[name], [_Floors].[FloorName] FROM [_Assets] LEFT JOIN [_Floors] ON [_Assets].[floor_id] = [_Floors].[floor_id] WHERE [_Floors].[FloorName] = 'Floor 1'